package site.zhanjingbo.HelloDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

public class HelloJDBC {
	static String DB_Driver = "com.mysql.jdbc.Driver";
	//?后接参数，characterEncoding表示使用的字符集
	static String DB_URL = "jdbc:mysql://localhost:3307/cloud_study?characterEncoding=utf8";
	static String DB_USER = "root";
	static String DB_PASSWORD = "";

	public static void main(String[] args) {
		
	}

	/**
	 * 基础JDBC操作demo
	 */
	public static void jdbcBase() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			// 1. 注册驱动
			Class.forName(DB_Driver);
			// 2. 获取Connection连接对象
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			// 3. 获取Statement对象
			stmt = conn.createStatement();
			// 4. 执行SQL语句并获得对应的Result对象
			rs = stmt.executeQuery("select username from user");
			// 5. 遍历Result对象
			while (rs.next()) {
				System.out.println("Hello " + rs.getString("username"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6. 关闭数据库连接对象，释放资源
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 使用游标可以每次从数据库读取结果集的部分数据，避免一次读入，造成JVM内存溢出
	 */
	public static void UseCursorFetch() {
		Connection conn = null;
		PreparedStatement ptmt = null;
		ResultSet rs = null;

		try {
			// 1. 注册驱动
			Class.forName(DB_Driver);
			// 2. 获取Connection连接对象
			DB_URL = DB_URL + "&useCursorFetch=true";
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			// 3. 获取Statement对象
			ptmt = conn.prepareStatement("select username from user");
			// 4. 设置游标大小
			ptmt.setFetchSize(2);
			// 5. 执行SQL语句并获得对应的Result对象
			rs = ptmt.executeQuery();
			// 6. 遍历Result对象
			while (rs.next()) {
				System.out.println("Hello " + rs.getString("username"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 7. 关闭数据库连接对象，释放资源
			try {
				if (rs != null) {
					rs.close();
				}
				if (ptmt != null) {
					ptmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 批处理Demo
	 * 
	 * @param users 需要添加的用户列表
	 */
	public static void useBarch(Set<String> users) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			// 1. 注册驱动
			Class.forName(DB_Driver);
			// 2. 获取Connection连接对象
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			// 3. 获取Statement对象
			stmt = conn.createStatement();
			
			// 4. 遍历用户列表构造添加用户的批处理
			for(String username:users){
				stmt.addBatch("insert into user(username) values('"+username+"')");
			}
			// 5. 执行批处理
			stmt.executeBatch();
			// 6. 对批处理信息进行清理
			stmt.clearBatch();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 7. 关闭数据库连接对象，释放资源
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 读取BLOB字段（大字段）
	 */
	public static void useBlob(){
		//采用ResultSet中的getInputStream方法来实现，目前没有合适的表，暂不实现。
	}

}
